﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Mads.WcfService
{
  partial class TrainBaseConnector
  {
    private const string addRoute = @"
INSERT INTO route(ID, RouteNumber, ExpressType, Regularity, PriceCoefficient, Obsolete,
  CreatedAt, Version)
VALUES (routeID, @routeNumber, @expressType, @regularity, @priceCoefficient, 
  0, NOW(), version)
WHERE routeID = (SELECT max(ID) + 1 FROM route) AND
version = (SELECT max(Version) + 1 FROM route WHERE RouteNumber = @routeNumber)";

    ///<summary>
    ///get next version of route
    ///</summary>
    private const string getNextRouteVersion = @"
SELECT max(Version) + 1 FROM route WHERE RouteNumber = @routeNumber";

    private const string addCar = @"
  INSERT INTO car(ID,  Number, RouteId, CarTypeId)
  VALUES carID, @number, routeId, @carTypeId
  WHERE carID = (SELECT max(ID) + 1 FROM car)
  AND routeId = (SELECT max(ID) FROM route WHERE route.RouteNumber = @routeNumber 
  ANDroute.Obsolete = 0)";

    /// <summary>
    /// get max car of the route
    /// </summary>
    private const string getMaxCar = @"
SELECT max(Number) FROM car WHERE RouteId = @routeID";

    private const string addRace = @"
INSERT INTO race(ID, Departure, RouteID)
SELECT max(ID) + 1, @departure, @routeID FROM  race";

    private const string addStation = @"
INSERT INTO station(ID, Name, Obsolete, CreatedAt, Version)
SELECT max(ID) + 1, @Name, 0, NOW(), @Version FROM station";

    ///<Summary>
    ///get next version of station 
    ///</Summary>
    private const string getNextStationVersion = @"
SELECT max(Version) + 1 FROM station 
WHERE Name = @name";

    private const string addRouteToStation = @"
  INSERT INTO routetostation(ID, Arrival, Departure, StationNumber, RouteID, StationID)
  VALUES routeToStationID, @arrival, @departure, @stationNumber, routeId, @stationID
  WHERE routeToStationID = (SELECT max(ID) + 1 FROM routeToStation)
  AND routeId = (SELECT max(ID) FROM route WHERE route.RouteNumber = @routeNumber)";

    private const string addCarType = @"
INSERT INTO cartype(ID, Name, PriceCoefficient, SeatsNumber, Obsolete)
SELECT max(ID) + 1, @name, @priceCoefficient, @sitsNumber, 0 FROM cartype";

    private const string addSeasonPrice = @"
INSERT INTO seasonprice(ID, StartDate, EndDate, PriceCoefficient, Obsolete,
  CreatedAt, Version)
SELECT max(ID) + 1, @startDate, @endDate, @priceCoefficient, 0, NOW(), @version FROM seasonprice";

    private const string addSit = @"
INSERT INTO sit(ID, Number, CarID, RaceID)
SELECT max(ID) + 1, @seatsNumber, @carID, @raceID FROM sit";

    private const string addStationPrice = @"
INSERT INTO stationprice(ID, StartStationID, EndStationID, Price, 
  Obsolete, CreatedAt, Version)
SELECT max(ID) + 1, @startStationID, @endStationID, @price, 0, NOW(), @version";

    /// <summary>
    /// get next version of station price by start date and end date
    /// </summary>
    private const string getNextStationPriceVersion = @"
SELECT max(Version) + 1 FROM stationprice 
WHERE StartStationID = @startStationID AND EndStationID = @endStationID";

    /// <summary>
    /// Query template for getting new ID for table.
    /// </summary>
    private const string getNextIDTemplate = @"SELECT max(ID) + 1 FROM {0}";

    private const string addTicketOwner = @"
INSERT INTO ticketowner(ID, Name, DocNumber) 
VALUES (@ID, @name, @docNumber)";

    private const string addUser = @"
INSERT INTO user(ID, Login, PasswordHash, Role)
SELECT max(ID) + 1, @login, @password, @role FROM user";

    private const string getUsers = @"
SELECT * FROM user WHERE Login = @login";

    private const string addTicket = @"
INSERT INTO ticket(ID, State, StartStationID, EndStationID, SitID, BuyerID,OwnerID)
SELECT max(ID) + 1, @state, @startStationID, @endStationID, @sitID, @buyerID, @ownerID FROM ticket";

    /// <summary>
    /// add tickets without owner
    /// </summary>
    private const string addFreeTicket = @"
INSERT INTO ticket(ID, State, StartStationID, EndStationID, SitID)
SELECT max(ID) + 1, 0, @startStationID, @endStationID, @sitID FROM ticket";

    /// <summary>
    /// get last creation of ticket
    /// </summary>
    private const string getLastDeparture = @"
SELECT max(Departure) FROM vwTicket";

    /// <summary>
    /// get information for seats
    /// </summary>
    private const string getSeatsInformation = @"
SELECT Car.ID, Race.ID, CarType.SitsNumber
FROM Car JOIN Route ON Car.RouteID = Route.ID
JOIN Race ON Route.ID = Race.RouteID
JOIN CarType ON Car.CarTypeID = CarType.ID
WHERE (Race.Departure >= @time1 AND Race.Departure < @time2)";

    ///<summary>
    ///get information about tickets
    ///</summary>
    private const string getTicketInformation = @"SELECT StartStop.ID, EndStop.ID, Sit.ID 
FROM RouteToStation StartStop JOIN Route R ON StartStop.RouteID = R.ID
JOIN RouteToStation EndStop ON R.ID = EndStop.RouteID
JOIN Race ON Race.RouteID = R.ID
JOIN Sit ON Sit.RaceID = Race.ID
WHERE (Race.Departure >= @time1 AND Race.Departure < @time2)
AND StartStop.StationNumber IN 
(SELECT min(RouteToStation.StationNumber) FROM RouteToStation JOIN Route R ON RouteToStation.RouteID = R.ID) 
AND EndStop.StationNumber IN
(SELECT max(RouteToStation.StationNumber) FROM RouteToStation JOIN Route R ON RouteToStation.RouteID = R.ID)";


    /// <summary>
    /// Query template for finding tickets by given stations, state and
    /// departure time interval.
    /// </summary>
    private const string findTicketsTemplate = @"
SELECT *, StartStop.Departure AS RealDeparture, EndStop.Arrival AS RealArrival
  FROM vwTicket T
  JOIN vwRaceToStation StartStop ON (StartStop.RaceID = T.RaceID AND StartStop.StationID = {0})
  JOIN vwRaceToStation EndStop ON (EndStop.RaceID = T.RaceID AND EndStop.StationID = {1})
  WHERE State = {2} AND (StartStop.Departure BETWEEN '{3}' AND '{4}')
    AND (StartStop.StationNumber BETWEEN T.StartStationNumber AND T.EndStationNumber)
    AND (EndStop.StationNumber BETWEEN T.StartStationNumber AND T.EndStationNumber)
    AND StartStop.StationNumber < EndStop.StationNumber
";

    /// <summary>
    /// Query template for getting season price by date.
    /// Takes one parameter: @date
    /// </summary>
    private const string getSeasonPrice = @"
 SELECT * FROM SeasonPrice
   WHERE (@date BETWEEN StartDate AND EndDate) AND Obsolete = 0
   LIMIT 1, 1
";

    /// <summary>
    /// Query template for getting ticket price (w/o multipliers)
    /// Takes two parameters: @startStopID, @endStopID
    /// </summary>
    private const string getTicketPrice = @"
SELECT * FROM StationsPrice
  WHERE StartStationID = @startStopID AND EndStationID = @endStopID
    AND Obsolete = 0
";

    /// <summary>
    /// Query template for getting full ticket information.
    /// Takes one parameter: @id
    /// </summary>
    private const string getTicketWithInfo = @"
SELECT T.*, Owner.Name AS Name, Owner.DocNumber AS DocNumber
  FROM vwTicket T
  LEFT JOIN TicketOwner Owner ON T.OwnerID = Owner.ID
  WHERE T.ID = @id
";

    /// <summary>
    /// Query template for getting bought ticket by its logicalID.
    /// Takes one parameter: @logicalID
    /// </summary>
    private const string getTicketByNumber = @"
SELECT T.*, Owner.Name AS Name, Owner.DocNumber AS DocNumber
  FROM vwTicket T
  JOIN TicketOwner Owner ON T.OwnerID = Owner.ID
  WHERE T.LogicalID = @logicalID
";
    private const string selectActualStations = @"
SELECT ID, Name FROM Station WHERE Obsolete = 0";

    /// <summary>
    /// make regularity 0 - everyday, 1 - odd, 2 - even
    /// </summary>
    private const string selectRoutesAndDeparture = @"
SELECT min(RouteToStation.Departure), Route.ID
FROM Route JOIN RouteToStation ON Route.ID = RouteToStation.RouteID 
WHERE Route.Obsolete = 0
GROUP BY Route.ID, Route.Regularity HAVING (Route.Regularity = 0 OR Route.Regularity = @reg)";

    /// <summary>
    /// Query template for buying ticket. Takes the following arguments:
    /// @id, @state, @startStopID, @endStopID, @ownerID, @buyerID, @bought, @logicalID
    /// </summary>
    private const string buyTicketTemplate = @"
UPDATE Ticket
SET State=@state, OwnerID=@ownerID, BuyerID = @buyerID,
    Bought = @bought, LogicalID = @logicalID,
    StartStationID=@startStopID, EndStationID=@endStopID
WHERE Id=@id
";

    /// <summary>
    /// Query template for finding free tickets adjacent to given one.
    /// Takes four argumets: @seatID, @state, @startStopID, @endStopID
    /// </summary>
    private const string getAdjacentFreeTickets = @"
SELECT ID, StartStationID, EndStationID
FROM Ticket
WHERE SitID = @seatID
  AND State = @state
  AND (StartStationID = @endStopID OR EndStationID = @startStopID)
";

    /// <summary>
    /// Query template for getting route stop on given route for given station.
    /// Takes two parameters: @routeID, @stationID
    /// </summary>
    private const string getRouteStopByRouteAndStation = @"
SELECT * FROM RouteToStation WHERE RouteID = @routeID AND stationID = @stationID
";
    private const string obsoleteSeasonPrice = @"
  UPDATE seasonprice SET Obsolete = 1";

    #region новые соединения для приложения для администратора

    private const string getAllUsers = @"
SELECT * FROM user";

    #endregion
  }
}